﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Model;
using System.Configuration;
using Dapper;
using System.Data.SqlClient;
using System.Data;

namespace DAL
{
    public class PersonDAL
    {
        public static readonly string SqlConnString = ConfigurationManager.ConnectionStrings["ELCO_ConnectionString"].ConnectionString;

        /// <summary>
        /// 获取角色名称  根据客户名称
        /// </summary>
        /// <returns></returns>
        public static List<RoleInfo> initRoleInfo(string customername)
        {
            using (var conn = new SqlConnection(SqlConnString))
            {
               
                string str = "select * from UserM_RoleInfo where CustomName='"+customername+"' ";
                return conn.Query<RoleInfo>(str).ToList();
            }

        }

        /// <summary>
        /// 保存用户信息
        /// </summary>
        /// <param name="UserNo"></param>
        /// <param name="UserName"></param>
        /// <param name="Password"></param>
        /// <param name="Tel"></param>
        /// <param name="CustomerName"></param>
        /// <param name="RoleName"></param>
        /// <returns></returns>
        public static string addPersonInfo(string UserNo, string UserName, string Password, string Tel, string CustomerName, string RoleName)
        {
            using (var conn = new SqlConnection(SqlConnString))
            {
                SqlCommand cmd = new SqlCommand();
                SqlTransaction transaction = null;
                try
                {
                    conn.Open();
                    transaction = conn.BeginTransaction();
                    cmd.Transaction = transaction;
                    cmd.Connection = conn;
                    cmd.CommandType = System.Data.CommandType.StoredProcedure;
                    SqlParameter[] sqlPara = new SqlParameter[7];

                    sqlPara[0] = new SqlParameter("@UserNo", UserNo);
                    sqlPara[0].Direction = System.Data.ParameterDirection.Input;
                    sqlPara[1] = new SqlParameter("@UserName", UserName);
                    sqlPara[1].Direction = System.Data.ParameterDirection.Input;
                    sqlPara[2] = new SqlParameter("@Password", Password);
                    sqlPara[2].Direction = System.Data.ParameterDirection.Input;
                    sqlPara[3] = new SqlParameter("@Tel ", Tel);
                    sqlPara[3].Direction = System.Data.ParameterDirection.Input;
                    sqlPara[4] = new SqlParameter("@CustomerName ", CustomerName);
                    sqlPara[4].Direction = System.Data.ParameterDirection.Input;
                    sqlPara[5] = new SqlParameter("@RoleName ", RoleName);
                    sqlPara[5].Direction = System.Data.ParameterDirection.Input;
                    sqlPara[6] = new SqlParameter("@CatchFlag", 0);
                    sqlPara[6].Size = 10;
                    sqlPara[6].Direction = System.Data.ParameterDirection.Output;

                    foreach (SqlParameter para in sqlPara)
                    {
                        cmd.Parameters.Add(para);
                    }
                    cmd.CommandText = "[usp_UserM_AddPersonInfo]";
                    cmd.ExecuteNonQuery();
                    transaction.Commit();
                    if (int.Parse(sqlPara[6].Value.ToString()) == 1)
                    {
                        return "hasexit";
                    }
                    return "success";
                }
                catch (Exception ex)
                {
                    transaction.Rollback();
                    return "falut";
                }
            }

        }

        /// <summary>
        /// 保存编辑 用户
        /// </summary>
        /// <param name="UserNo"></param>
        /// <param name="UserName"></param>
        /// <param name="Password"></param>
        /// <param name="Tel"></param>
        /// <param name="CustomerName"></param>
        /// <param name="RoleName"></param>
        /// <returns></returns>
        public static string editPersonInfo(string UserNo, string UserName, string Password, string Tel, string CustomerName, string RoleName)
        {
            using (var conn = new SqlConnection(SqlConnString))
            {
                SqlCommand cmd = new SqlCommand();
                SqlTransaction transaction = null;
                try
                {
                    conn.Open();
                    transaction = conn.BeginTransaction();
                    cmd.Transaction = transaction;
                    cmd.Connection = conn;
                    cmd.CommandType = System.Data.CommandType.StoredProcedure;
                    SqlParameter[] sqlPara = new SqlParameter[6];

                    sqlPara[0] = new SqlParameter("@UserNo", UserNo);
                    sqlPara[0].Direction = System.Data.ParameterDirection.Input;
                    sqlPara[1] = new SqlParameter("@UserName", UserName);
                    sqlPara[1].Direction = System.Data.ParameterDirection.Input;
                    sqlPara[2] = new SqlParameter("@Password", Password);
                    sqlPara[2].Direction = System.Data.ParameterDirection.Input;
                    sqlPara[3] = new SqlParameter("@Tel ", Tel);
                    sqlPara[3].Direction = System.Data.ParameterDirection.Input;
                    sqlPara[4] = new SqlParameter("@CustomerName ", CustomerName);
                    sqlPara[4].Direction = System.Data.ParameterDirection.Input;
                    sqlPara[5] = new SqlParameter("@RoleName ", RoleName);
                    sqlPara[5].Direction = System.Data.ParameterDirection.Input;
                   
                    foreach (SqlParameter para in sqlPara)
                    {
                        cmd.Parameters.Add(para);
                    }
                    cmd.CommandText = "[usp_UserM_EditPersonInfo]";
                    cmd.ExecuteNonQuery();
                    transaction.Commit();
                   
                    return "success";
                }
                catch (Exception ex)
                {
                    transaction.Rollback();
                    return "falut";
                }
            }

        }


        /// <summary>
        /// 删除用户信息
        /// </summary>
        /// <param name="UserID"></param>
        /// <returns></returns>
        public static int deletePersonInfo(string UserID)
        {
            int affecgtRow = 0;
            
            using (var conn = new SqlConnection(SqlConnString))
            {
                string sql = "DELETE FROM  UserInfo WHERE ID = @UserID;";
                affecgtRow = conn.Execute(sql, new { UserID = UserID });
            }
            return affecgtRow;
        }
        /// <summary>
        /// 获取用户列表  根据所属单位
        /// </summary>
        /// <param name="sortname"></param>
        /// <param name="sortorder"></param>
        /// <param name="keyvalue"></param>
        /// <param name="CustomerName"></param>
        /// <returns></returns>
        public static DataTable getPersonInfo(string sortname, string sortorder, string keyvalue, string CustomerName)
        {
            string str = "";
            DataTable dt = new DataTable();
            using (var conn = new SqlConnection(SqlConnString))
            {
                SqlCommand cmd = new SqlCommand();
                conn.Open();
                cmd.Connection = conn;
                if (CustomerName == "宏大")
                {
                    str = "select a.ID,a.UserNo,a.UserName,a.Tel,b.RoleName,a.CustomerName from UserInfo  a left join UserM_RoleInfo  b on  a.RoleName= b.ID  where 1=1 ";
                    if (keyvalue != "")
                    {
                        str += " and a.UserNo like '%" + keyvalue + "%'";
                    }
                    str += "order by a." + sortname + " " + sortorder + "";
                    cmd.CommandType = CommandType.Text;
                    cmd.CommandText = str;
                    SqlDataAdapter Datapter = new SqlDataAdapter(cmd);
                    Datapter.Fill(dt);
                    return dt;
                }
                else
                {
                    str = "select a.ID,a.UserNo,a.UserName,a.Tel,b.RoleName,a.CustomerName from UserInfo a left join UserM_RoleInfo  b on  a.RoleName= b.ID where a.CustomerName='" + CustomerName + "' ";
                    if (keyvalue != "")
                    {
                        str += " and a.UserNo like '%" + keyvalue + "%'";
                    }
                    str += "order by a." + sortname + " " + sortorder + "";
                    cmd.CommandType = CommandType.Text;
                    cmd.CommandText = str;
                    SqlDataAdapter Datapter = new SqlDataAdapter(cmd);
                    Datapter.Fill(dt);
                    return dt;

                }

            }

        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="PersonID"></param>
        /// <returns></returns>
        public static List<UserEditInfo> initeditperson(string PersonID)
        {
            using (var conn = new SqlConnection(SqlConnString))
            {

                string str = "select userinfo.ID,UserNo,UserName,UserPwd,userinfo.Tel,CustomerName,role.RoleName from UserInfo userinfo left join UserM_RoleInfo role on userinfo.RoleName = role.ID where  userinfo.ID='" + PersonID + "' ";
                return conn.Query<UserEditInfo>(str).ToList();
            }

        }
    }
}
